	[ 7-1]
CREATE TABLE  (
	̵  VARCHAR(20)	 NOT NULL,
	̸    VARCHAR(10)	 NOT NULL,
		    INT,
		    VARCHAR(10)	 NOT NULL,
		    VARCHAR(20),
		    INT   DEFAULT 0,
	PRIMARY KEY(̵)
);


	[ 7-2]
CREATE TABLE  ǰ (
	ǰȣ   CHAR(3)   NOT NULL,
	ǰ     VARCHAR(20),
	     INT,
	ܰ       INT,
	ü   VARCHAR(20),
	PRIMARY KEY(ǰȣ),
	CHECK ( >= 0 AND  <=10000)
);


	[ 7-3]----> MS SQL  2019     
CREATE TABLE  ֹ (
	ֹȣ   CHAR(3) NOT NULL,
	ֹ   VARCHAR(20),
	ֹǰ   CHAR(3),
	       INT,
	     VARCHAR(30),
	ֹ   DATE,
	PRIMARY KEY(ֹȣ),
	FOREIGN KEY(ֹ)   REFERENCES   (̵),
	FOREIGN KEY(ֹǰ)   REFERENCES   ǰ(ǰȣ)
);


CREATE TABLE  ֹ (
	ֹȣ   CHAR(3) NOT NULL,
	ֹ   VARCHAR(20),
	ֹǰ   CHAR(3),
	       INT,
	     VARCHAR(30),
	ֹ   DATETIME,
	PRIMARY KEY(ֹȣ),
	FOREIGN KEY(ֹ)   REFERENCES   (̵),
	FOREIGN KEY(ֹǰ)   REFERENCES   ǰ(ǰȣ)
);


	[ 7-4]
CREATE TABLE  ۾ü (
	üȣ CHAR(3) NOT NULL,
	ü   VARCHAR(20),
	ּ     VARCHAR(100),
	ȭȣ VARCHAR(20),
	PRIMARY KEY(üȣ)
);


	[ 7-5]----> MS SQL  2019     
ALTER TABLE  ADD Գ¥ DATE; 

ALTER TABLE  ADD Գ¥ DATETIME; 


	[ 7-6]
ALTER TABLE  DROP COLUMN Գ¥;


	[ 7-7]
ALTER TABLE  ADD CONSTRAINT CHK_AGE CHECK( >= 20); 


	[ 7-8]
ALTER TABLE  DROP CONSTRAINT CHK_AGE; 


	[ 7-9]
DROP TABLE ۾ü; 


	[ 7-10 ]
[ ̺  ]
INSERT INTO  VALUES ('apple', 'ȭ', 20, 'gold', 'л', 1000);
INSERT INTO  VALUES ('banana', '輱', 25, 'vip', 'ȣ', 2500);
INSERT INTO  VALUES ('carrot', '', 28, 'gold', '', 4500);
INSERT INTO  VALUES ('orange', '', 22, 'silver', 'л', 0);
INSERT INTO  VALUES ('melon', '', 35, 'gold', 'ȸ', 5000);
INSERT INTO  VALUES ('peach', '', NULL, 'silver', 'ǻ', 300);
INSERT INTO  VALUES ('pear', 'ä', 31, 'silver', 'ȸ', 500);

[ǰ ̺  ]
INSERT INTO ǰ VALUES ('p01', '׳ɸ', 5000, 4500, 'ѽǰ');
INSERT INTO ǰ VALUES ('p02', 'ſ̸', 2500, 5500, 'αǪ');
INSERT INTO ǰ VALUES ('p03', '', 3600, 2600, 'Ѻ');
INSERT INTO ǰ VALUES ('p04', 'ݸ', 1250, 2500, 'Ѻ');
INSERT INTO ǰ VALUES ('p05', 'ū', 2200, 1200, 'ѽǰ');
INSERT INTO ǰ VALUES ('p06', '쵿', 1000, 1550, 'αǪ');
INSERT INTO ǰ VALUES ('p07', '޺Ŷ', 1650, 1500, 'Ѻ');

[ֹ ̺  ]
INSERT INTO ֹ VALUES ('o01', 'apple', 'p03', 10, ' ', '22/01/01');
INSERT INTO ֹ VALUES ('o02', 'melon', 'p01', 5, 'õ 籸', '22/01/10');
INSERT INTO ֹ VALUES ('o03', 'banana', 'p06', 45, '⵵ õ', '22/01/11');
INSERT INTO ֹ VALUES ('o04', 'carrot', 'p02', 8, 'λ ', '22/02/01');
INSERT INTO ֹ VALUES ('o05', 'melon', 'p06', 36, '⵵ ν', '22/02/20');
INSERT INTO ֹ VALUES ('o06', 'banana', 'p01', 19, 'ûϵ ', '22/03/02');
INSERT INTO ֹ VALUES ('o07', 'apple', 'p03', 22, ' ', '22/03/15');
INSERT INTO ֹ VALUES ('o08', 'pear', 'p02', 50, ' õ', '22/04/10');
INSERT INTO ֹ VALUES ('o09', 'banana', 'p04', 15, '󳲵 ', '22/04/11');
INSERT INTO ֹ VALUES ('o10', 'carrot', 'p03', 20, '⵵ Ⱦ', '22/05/22');


	[ 7-10]
SELECT	̵, ̸, 
FROM	;


	[ 7-11]
SELECT	̵, ̸, , , , 
FROM	;


	[ 7-12]
SELECT	*
FROM	;


	[ 7-13]
SELECT	ü
FROM	ǰ;


	[ 7-14]
SELECT	ALL   ü
FROM  ǰ;


	[ 7-15]
SELECT	DISTINCT   ü
FROM	ǰ;


	[ 7-16]
SELECT	ǰ, ܰ AS 
FROM	ǰ;


	[ 7-17]----> MS SQL  2019     
SELECT	ǰ, ܰ + 500 AS " ܰ"
FROM	ǰ;


SELECT	ǰ, ܰ + 500 AS ' ܰ'
FROM	ǰ;


	[ 7-18]
SELECT	ǰ, , ܰ
FROM	ǰ
WHERE 	ü = 'Ѻ'; 


	[ 7-19]
SELECT	ֹǰ, , ֹ
FROM	ֹ
WHERE	ֹ = 'apple'  AND   >= 15;


	[ 7-20]
SELECT  ֹǰ, , ֹ, ֹ
FROM    ֹ
WHERE   ֹ = 'apple'  OR   >= 15;


	[ 7-21]
SELECT	ǰ, ܰ, ü
FROM	ǰ
WHERE	ܰ >= 2000  AND  ܰ <= 3000;


	[ 7-22]
SELECT	̸, , , 
FROM	
WHERE	̸ LIKE '%';


	[ 7-23]
SELECT	̵, ̸, 
FROM	
WHERE	̵ LIKE '_____';


	[ 7-24]
SELECT	̸
FROM	
WHERE	 IS NULL;


	[ 7-25]
SELECT	̸
FROM	
WHERE	 IS NOT NULL;


	[ 7-26]
SELECT	̸, , 
FROM	
ORDER BY     DESC;


	[ 7-27]
SELECT	ֹ, ֹǰ, , ֹ
FROM	ֹ
WHERE	 >= 10
ORDER BY  ֹǰ ASC,  DESC;


	[ 7-28]
SELECT	AVG(ܰ)
FROM	ǰ;


	[ 7-29]----> MS SQL  2019     
SELECT	SUM()  AS  " հ"
FROM	ǰ
WHERE	ü = 'Ѻ';


SELECT	SUM()  AS  ' հ'
FROM	ǰ
WHERE	ü = 'Ѻ';


	[ 7-30]
SELECT	COUNT(̵) AS 
FROM	;

SELECT	COUNT() AS 
FROM	;

SELECT	COUNT(*) AS 
FROM	;


	[ 7-31]----> MS SQL  2019     
SELECT	COUNT(DISTINCT ü) AS "ü "
FROM	ǰ;


SELECT	COUNT(DISTINCT ü) AS 'ü ' 
FROM	ǰ;


	[ 7-32]
SELECT	ֹǰ, SUM() AS ֹ
FROM	ֹ
GROUP BY   ֹǰ;


SELECT	SUM() AS ֹ
FROM	ֹ
GROUP BY   ֹǰ;


	[ 7-33]
SELECT	ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM	ǰ
GROUP BY   ü;


	[ 7-34]
SELECT	ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM	ǰ
GROUP BY  ü  HAVING COUNT(*) >= 3;


	[ 7-35]
SELECT	, COUNT(*) AS , AVG() AS 
FROM	
GROUP BY    HAVING AVG() >= 1000;


	[ 7-36]
SELECT	ֹǰ, ֹ, SUM() AS ֹ
FROM	ֹ
GROUP BY  ֹǰ, ֹ; 


	[ 7-37]
SELECT	ǰ.ǰ
FROM	ǰ, ֹ
WHERE	ֹ.ֹ = 'banana'  AND  ǰ.ǰȣ = ֹ.ֹǰ; 


	[ 7-38]
SELECT	ֹ.ֹǰ, ֹ.ֹ
FROM	, ֹ
WHERE	. >= 30  AND  .̵ = ֹ.ֹ; 


SELECT  o.ֹǰ, o.ֹ
FROM      c, ֹ o
WHERE   c. >= 30  AND  c.̵ = o.ֹ; 


SELECT  ֹ.ֹǰ, ֹ.ֹ
FROM      INNER JOIN  ֹ  ON  .̵ = ֹ.ֹ
WHERE   . >= 30;


	[ 7-39]
SELECT	ǰ.ǰ
FROM	, ǰ, ֹ
WHERE	.̸ = '' AND  .̵ = ֹ.ֹ 
	 AND  ǰ.ǰȣ = ֹ.ֹǰ; 


[ܺ  ]
SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	   LEFT OUTER JOIN  ֹ  ON  .̵ = ֹ.ֹ;

SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	 ֹ  RIGHT OUTER JOIN    ON  ֹ.ֹ = .̵;


	[ 7-40]
SELECT	ǰ, ܰ
FROM	ǰ
WHERE	ü = (SELECT  ü
                    FROM    ǰ
                    WHERE   ǰ = '޺Ŷ');

SELECT	ǰ, ܰ
FROM	ǰ
WHERE	ü = 'Ѻ';


	[ 7-41]
SELECT	̸, 
FROM	
WHERE	 = (SELECT  MAX()  FROM  ); 

SELECT	̸, 
FROM	
WHERE	 = 5000; 


	[ 7-42]
SELECT	ǰ, ü
FROM	ǰ
WHERE	ǰȣ  IN (SELECT  ֹǰ   
		      FROM   ֹ
		      WHERE  ֹ  = 'banana'); 

SELECT	ǰ, ü
FROM	ǰ
WHERE	ǰȣ  IN  ('p01', 'p04', 'p06'); 


[ Ǹ ̿ SELECT ]
SELECT	 ǰ.ǰ, ǰ.ü
FROM	 ǰ, ֹ
WHERE	 ǰ.ǰȣ = ֹ.ֹǰ  AND  ֹ.ֹ  = 'banana'; 


[EXISTS ڸ ̿ SELECT ]
SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 EXISTS  (SELECT  *   
                  FROM   ֹ
                  WHERE  ǰ.ǰȣ = ֹ.ֹǰ  
		           AND  ֹ.ֹ  = 'banana'); 


	[ 7-43]
SELECT	ǰ, ü
FROM	ǰ
WHERE	ǰȣ  NOT  IN (SELECT  ֹǰ   
			   FROM	   ֹ
			   WHERE   ֹ  = 'banana'); 


	[ 7-44]
SELECT	ǰ, ܰ, ü
FROM	ǰ
WHERE	ܰ  > ALL (SELECT   ܰ   
		     FROM     ǰ
		     WHERE    ü  = 'ѽǰ'); 


	[ 7-45]
SELECT	̸
FROM	
WHERE	EXISTS (SELECT  *   
	        FROM    ֹ
	        WHERE   ֹ = '2022-03-15' 
		        AND  ֹ.ֹ = .̵); 


	[ 7-46]
SELECT	̸
FROM	
WHERE	NOT  EXISTS (SELECT  *   
		     FROM    ֹ
		     WHERE   ֹ = '2022-03-15' 
  	       	             AND  ֹ.ֹ = .̵); 


	[ 7-47]
INSERT  
INTO	(̵, ̸, , , , )
VALUES ('strawberry', '', 30, 'vip', '', 100);

SELECT  *  FROM  ;  


	[ 7-48]
INSERT  
INTO    (̵, ̸, , , )
VALUES ('tomato', '', 36, 'gold', 4000);

SELECT  *  FROM  ;  


[μ ǹ ̿  ]
CREATE TABLE  Ѻǰ (
	ǰ      VARCHAR(20),
	      INT,
	ܰ        INT,
	PRIMARY KEY(ǰ)
);


INSERT  
INTO    Ѻǰ(ǰ, , ܰ)
SELECT  ǰ, , ܰ
FROM    ǰ
WHERE   ü = 'Ѻ';

SELECT  *  FROM  Ѻǰ;  


	[ 7-49]
UPDATE	ǰ  
SET	ǰ = 'ū'
WHERE	ǰȣ = 'p03';

SELECT  *  FROM  ǰ;  


	[ 7-50]
UPDATE	ǰ  
SET	ܰ = ܰ * 1.1;

SELECT  *  FROM  ǰ;  


	[ 7-51]
UPDATE	ֹ  
SET	 = 5
WHERE   ֹ IN (SELECT       ̵
 		     FROM         
		     WHERE        ̸ = 'ȭ');

SELECT  *  FROM	ֹ; 


	[ 7-52]
DELETE
FROM	ֹ  
WHERE	ֹ = '2022-05-22';

SELECT  *  FROM  ֹ; 


	[ 7-53]
DELETE     
FROM	ֹ
WHERE	ֹ IN (SELECT  ̵
 	             FROM    
		     WHERE   ̸ = 'ȭ');

SELECT  *  FROM  ֹ;  


	[ 7-54]
DELETE
FROM	 ֹ;

SELECT  *  FROM  ֹ; 



	[ 7-55]
CREATE VIEW   (̵, ̸, , )
AS SELECT     ̵, ̸, , 
   FROM       
   WHERE       = 'vip'
WITH CHECK OPTION;

SELECT  *  FROM    ;



CREATE VIEW	
AS SELECT	̵, ̸, , 
   FROM		
   WHERE	 = 'vip'
WITH CHECK OPTION;


	[ 7-56]
CREATE  VIEW	   üǰ(ü, ǰ)
AS  SELECT	   ü, COUNT(*)
      FROM	   ǰ
      GROUP BY     ü
WITH CHECK OPTION;

SELECT  *  FROM	   üǰ;


	[ 7-57]
SELECT  *  FROM    WHERE   >= 20;


CREATE VIEW	ǰ1
AS SELECT	ǰȣ, , ü
   FROM		ǰ
WITH CHECK OPTION;

SELECT * FROM	ǰ1;



CREATE VIEW	ǰ2
AS SELECT	ǰ, , ü
   FROM		ǰ
WITH CHECK OPTION;

SELECT * FROM	ǰ2;


	[ 7-58]
INSERT INTO	ǰ1	VALUES ('p08', 1000, 'żǰ');

SELECT * FROM	ǰ1; 

SELECT * FROM	ǰ;


	[ 7-59]
DROP VIEW ;